﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Cstudy.COM
{
    public class SQLHelper
    {
        private static SqlConnection createcon()
        {
            SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ToString());
            return sqlcon;
        }


        /// <summary>
        /// 增删改数据
        /// </summary>
        /// <param name="storagename">存储过程名</param>
        /// <param name="parms">存储过程参数</param>
        /// <returns>bool 成功与否</returns>
        public static bool ExecuteNonQuery(string storagename, SqlParameter[] parms)
        {
            SqlConnection con = createcon();
            SqlCommand com = new SqlCommand();
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = storagename;
            try
            {
                con.Open();
                com.Connection = con;
                for (int i = 0; i < parms.GetLength(0); i++)
                {
                    com.Parameters.Add(parms[i]);
                }
                if (com.ExecuteNonQuery() > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (SqlException ex)
            {
                return false;
            }
            finally
            {
                con.Dispose();
                con.Close();
                com.Dispose();
            }
        }
        /// <summary>
        /// 增删改数据
        /// </summary>
        /// <param name="storagename">存储过程名</param>
        /// <returns>bool 成功与否</returns>
        public static bool ExecuteNonQuery(string storagename)
        {
            SqlConnection con = createcon();
            SqlCommand com = new SqlCommand();
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = storagename;
            try
            {
                con.Open();
                com.Connection = con;
                if (com.ExecuteNonQuery() > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (SqlException ex)
            {
                return false;
            }
            finally
            {
                con.Dispose();
                con.Close();
                com.Dispose();
            }
        }
        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="storagename">存储过程名</param>
        /// <param name="parms">存储过程参数</param>
        /// <returns>返回查询数据</returns>
        public static DataSet ExecuteQuery(string storagename,SqlParameter[] parms)
        {
            SqlConnection con = createcon();
            SqlCommand com=new SqlCommand();
            com.CommandType=CommandType.StoredProcedure;
            com.CommandText=storagename;
            SqlDataAdapter da=new SqlDataAdapter();
            DataSet ds=new DataSet();
            try
            {
                con.Open();
                com.Connection=con;
                for(int i=0;i<parms.GetLength(0);i++)
                {
                    com.Parameters.Add(parms[i]);
                }
                da.SelectCommand=com;
                da.Fill(ds);
            }
            catch(SqlException ex)
            {
                return null;
            }
            finally
            {
                con.Dispose();
                con.Close();
                com.Dispose();
                da.Dispose();
            }
            return ds;
        }
        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="storagename">存储过程名</param>
        /// <returns>返回查询数据</returns>
        public static DataSet ExecuteQuery(string storagename)
        {
            SqlConnection con = createcon();
            SqlCommand com = new SqlCommand();
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = storagename;
            SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds = new DataSet();
            try
            {
                con.Open();
                com.Connection = con;
                da.SelectCommand = com;
                da.Fill(ds);
            }
            catch (SqlException ex)
            {
                return null;
            }
            finally
            {
                con.Dispose();
                con.Close();
                com.Dispose();
                da.Dispose();
            }
            return ds;
        }

        /// <summary>
        /// 查询单行数据
        /// </summary>
        /// <param name="storagename">存储过程名</param>
        /// <param name="parms">存储过程参数</param>
        /// <returns>返回查询数据</returns>
        public static object ExecuteScalar(string storagename, SqlParameter[] parms)
        {
            SqlConnection con = createcon();
            SqlCommand com = new SqlCommand();
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = storagename;
            try
            {
                con.Open();
                com.Connection = con;
                for (int i = 0; i < parms.GetLength(0); i++)
                {
                    com.Parameters.Add(parms[i]);
                }
                return com.ExecuteScalar();
            }
            catch (SqlException ex)
            {
                return null;
            }
            finally
            {
                con.Dispose();
                con.Close();
                com.Dispose();
            }
        }
        /// <summary>
        /// 执行带返回值存储过程
        /// </summary>
        /// <param name="storagename">存储过程名</param>
        /// <param name="parms">存储过程参数</param>
        /// <param name="result">返回值，整型</param>
        /// <param name="restring">返回参数名</param>
        /// <returns>是否执行成功</returns>
        public static bool ExecuteNonQuery(string storagename, SqlParameter[] parms,out int result,string restring)
        {
            SqlConnection con = createcon();
            SqlCommand com = new SqlCommand();
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = storagename;
            try
            {
                con.Open();
                com.Connection = con;
                for (int i = 0; i < parms.GetLength(0); i++)
                {
                    com.Parameters.Add(parms[i]);
                }
                if (com.ExecuteNonQuery() > 0)
                {
                    result = (int)(com.Parameters[restring].Value);
                    return true;
                }
                else
                {
                    result = 0;
                    return false;
                }
            }
            catch (SqlException ex)
            {
                result = 0;
                return false;
            }
            finally
            {
                con.Dispose();
                con.Close();
                com.Dispose();
            }
        }
    }
}
